export default {
  // 增删改查
  querySql(model) {
    // 结构数据
    const { table, data, query, reverse, key, sort, pid, type } = model
    // 分页
    let show = 10
    let page = 1
    if (query) {
      page = query.page
      show = query.show
    }
    // let limit = ' LIMIT ' + (page - 1) * show + ',' + show
    let limit = ` LIMIT ${(page - 1) * show}, ${show}`
    let orders = []

    // 条件查询 （普通查询）
    let fileds = '*'
    if (key && key != '') {
      fileds = key
    }
    let condition = ''
    if (data) {
      for (let i in data) {
        if (data[i] && data[i] != '-1') {
          condition += ` AND ${i}="${data[i]}"`
        }
      }
    } else {
      condition = ' '
    }

    // 模糊查询
    let vague = ''
    // if (!words) {
    //   vague = ' '
    // } else {
    //   let attr = ''
    //   let value = ''
    //   for (let i in words) {
    //     attr += '`' + i + '`,'
    //     value = words[i]
    //   }
    //   attr = attr.slice(0, attr.length - 1)
    //   vague = 'AND CONCAT( ' + attr + ") LIKE '%" + value + "%'"
    // }

    // 倒序
    if (reverse) {
      orders.push('id DESC')
    }

    if (pid) {
      orders.push('pid ASC')
    }

    if (type) {
      orders.push('type ASC')
    }

    if (sort) {
      orders.push('sort ASC')
    }
    let sql = ''
    // 最终查询
    if (orders.length > 0) {
      sql = `SELECT ${fileds} FROM ${table} WHERE delid=1${vague + condition} ORDER BY ${orders.join(',')}`
    } else {
      sql = `SELECT ${fileds} FROM ${table} WHERE delid=1${vague + condition}`
    }
    return {
      all: sql,
      sql: sql + limit
    }
  },
  addSql(model) {
    const { table, data } = model
    let a = [], b = []
    for (let i in data) {
      a.push('`' + i + '`')
      b.push("'" + data[i] + "'")
    }
    let sql = `INSERT INTO ${table} (${a.join(',')}) VALUES(${b.join(',')})`
    return sql
  },
  editSql(model) {
    const { table, data } = model
    let a = []
    for (let i in data) {
      if (i != 'id') {
        a.push(i + "='" + data[i] + "'")
      }
    }
    return `UPDATE ${table} SET ${a.join(',')} WHERE id= '${data.id}'`
  },
  delSql(model) {
    const { table, id } = model
    return `UPDATE ${table} SET delid = 0 WHERE id IN ( ${id} )`
  },
  // 系统sql
  allTableSql() {
    return 'SELECT name FROM sqlite_master WHERE type="table";'
  },
  allDataSql(table) {
    return `SELECT * from ${table} WHERE delid=1`
  },
  infoSql(model) {
    return `PRAGMA table_info(${model.table});`
  },
  columnsSql(table) {
    return `SELECT * from columns WHERE delid=1 AND tname='${table}'`
  },
  // 权限树
  batchSortSql(model) {
    let { table, sorts } = model
    const ids = [], arr = []
    sorts.forEach(x => {
      ids.push(x.id)
      arr.push(
        `WHEN id = ${x.id} THEN ${x.sort}`
      )
    });
    return `
      UPDATE ${table}
      SET sort = CASE
      ${arr.join('\n')}
      END
      WHERE id IN (${ids.join(',')});
    `
  },
  treeSortSql(model) {
    let { table, sorts } = model
    const ids = [], arr1 = [], arr2 = []
    sorts.forEach(x => {
      ids.push(x.id)
      arr1.push(
        `WHEN id = ${x.id} THEN ${x.pid}`
      )
      arr2.push(
        `WHEN id = ${x.id} THEN ${x.sort}`
      )
    });
    return `
      UPDATE ${table} SET
      pid = CASE
      ${arr1.join('\n')}
      END,
      sort = CASE
      ${arr2.join('\n')}
      END
      WHERE id IN (${ids.join(',')});
    `
  }
}
